﻿using System;
using System.Collections;
using System.Linq;
using System.Web;
using System.IO;
using System.Data.SqlClient;
using System.Data;
using System.Xml;
using System.Globalization;

/// <summary>
/// Summary description for DB
/// </summary>
public class DB
{
    public DB()
    {
        //  
        // TODO: Add constructor logic here
        //
    }

    private static SqlConnection OpenConnection()
    {
        SqlConnection conn = null;

        try
        {
            conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=PacificStarBPO;Integrated Security=True;Pooling=False");
            conn.Open();
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        return conn;
    }

    public static DataSet getUserDataDS()
    {
        DataSet result = new DataSet();
        RE_GenericBean rgb = null;
        result.Tables.Add("users");
        result.Tables["users"].Columns.Add("User ID");
        result.Tables["users"].Columns.Add("Password");
        result.Tables["users"].Columns.Add("First Name");
        result.Tables["users"].Columns.Add("Last Name");
        result.Tables["users"].Columns.Add("Short Name");
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = "SELECT * fROM tbl_usuario_descripcion";
            reader = comm.ExecuteReader();

            while (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.strC1 = reader.GetString(0);
                if (!reader.IsDBNull(1)) rgb.strC2 = reader.GetString(1);
                if (!reader.IsDBNull(2)) rgb.strC3 = reader.GetString(2);
                if (!reader.IsDBNull(3)) rgb.strC4 = reader.GetString(3);
                if (!reader.IsDBNull(4)) rgb.strC5 = reader.GetString(4);
                object[] objArr = { rgb.strC1, rgb.strC2, rgb.strC3, rgb.strC4, rgb.strC5 };
                result.Tables["users"].Rows.Add(objArr);
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }


    public static Hashtable getContadorData()
    {
        Hashtable result = new Hashtable();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        RE_GenericBean rgb = null;
        RE_GenericBean rgb2 = null;
        DateTime lunes, martes, miercoles, jueves, viernes, sabado, domingo;
        try
        {
            DateTime ahora = DateTime.Now;
            System.Globalization.Calendar c = CultureInfo.CurrentCulture.Calendar;
            int semana = c.GetWeekOfYear(ahora, CalendarWeekRule.FirstDay, ahora.DayOfWeek);

            lunes = Utility.FirstDateOfWeek(ahora.Year, semana, 1);
            martes = Utility.FirstDateOfWeek(ahora.Year, semana, 2);
            miercoles = Utility.FirstDateOfWeek(ahora.Year, semana, 3);
            jueves = Utility.FirstDateOfWeek(ahora.Year, semana, 4);
            viernes = Utility.FirstDateOfWeek(ahora.Year, semana, 5);
            sabado = Utility.FirstDateOfWeek(ahora.Year, semana, 6);
            domingo = Utility.FirstDateOfWeek(ahora.Year, semana, 7);

            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            //comm.CommandText = "select count(*), agente, fecha from (select dat_xml.query('//campos/frmAgentName').value('.', 'varchar(max)') as agente, dat_xml.query('//campos/frmTypeSale').value('.', 'varchar(max)') as tipo, dat_xml.query('//campos/frmQCDate').value('.', 'datetime') as fecha from tbl_datos where (datepart(WEEK,dat_xml.query('//campos/frmQCDate').value('.', 'datetime'))=DATEPART(WEEK,GETDATE())) and dat_xml.query('//campos/frmQCStatus').value('.', 'varchar(max)')='APPROVED') tmp group by agente, fecha";
            comm.CommandText = "select count(*), agente, fecha, tipo from (select dat_xml.query('//campos/frmAgentName').value('.', 'varchar(max)') as agente, dat_xml.query('//campos/frmTypeSale').value('.', 'varchar(max)') as tipo, dat_xml.query('//campos/frmQCDate').value('.', 'datetime') as fecha from tbl_datos where (datepart(WEEK,dat_xml.query('//campos/frmQCDate').value('.', 'datetime'))=DATEPART(WEEK,GETDATE())) and dat_xml.query('//campos/frmQCStatus').value('.', 'varchar(max)')='APPROVED') tmp, tbl_usuario_perfil, tbl_usuario_descripcion where ude_id=upe_ude_id and upe_per_id=3 and ude_nombre + ' ' +ude_apellido=agente group by agente, tipo, fecha";
            reader = comm.ExecuteReader();

            while (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0);
                if (!reader.IsDBNull(1)) rgb.strC1 = reader.GetString(1);
                if (!reader.IsDBNull(2)) rgb.strC2 = reader.GetDateTime(2).ToShortDateString();
                if (!reader.IsDBNull(3)) rgb.strC3 = reader.GetString(3);

                if (result.ContainsKey(rgb.strC1))
                {
                    rgb2 = (RE_GenericBean)result[rgb.strC1];
                    if (rgb.strC3.Equals("DOUBLE")) rgb.intC1 = rgb.intC1 * 2;
                    if (lunes.ToShortDateString().Equals(rgb.strC2)) rgb2.intC1 += rgb.intC1;
                    if (martes.ToShortDateString().Equals(rgb.strC2)) rgb2.intC2 += rgb.intC1;
                    if (miercoles.ToShortDateString().Equals(rgb.strC2)) rgb2.intC3 += rgb.intC1;
                    if (jueves.ToShortDateString().Equals(rgb.strC2)) rgb2.intC4 += rgb.intC1;
                    if (viernes.ToShortDateString().Equals(rgb.strC2)) rgb2.intC5 += rgb.intC1;
                    if (sabado.ToShortDateString().Equals(rgb.strC2)) rgb2.intC6 += rgb.intC1;
                    if (domingo.ToShortDateString().Equals(rgb.strC2)) rgb2.intC7 += rgb.intC1;
                    result.Remove(rgb.strC1);
                    result.Add(rgb.strC1, rgb2);
                }
                else
                {
                    rgb2 = new RE_GenericBean();
                    if (rgb.strC3.Equals("DOUBLE")) rgb.intC1 = rgb.intC1 * 2;
                    rgb2.strC1 = rgb.strC1;
                    if (lunes.ToShortDateString().Equals(rgb.strC2)) rgb2.intC1 = rgb.intC1;
                    if (martes.ToShortDateString().Equals(rgb.strC2)) rgb2.intC2 = rgb.intC1;
                    if (miercoles.ToShortDateString().Equals(rgb.strC2)) rgb2.intC3 = rgb.intC1;
                    if (jueves.ToShortDateString().Equals(rgb.strC2)) rgb2.intC4 = rgb.intC1;
                    if (viernes.ToShortDateString().Equals(rgb.strC2)) rgb2.intC5 = rgb.intC1;
                    if (sabado.ToShortDateString().Equals(rgb.strC2)) rgb2.intC6 = rgb.intC1;
                    if (domingo.ToShortDateString().Equals(rgb.strC2)) rgb2.intC7 = rgb.intC1;
                    result.Add(rgb.strC1, rgb2);
                }
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static Hashtable getContadorDataMP()
    {
        Hashtable result = new Hashtable();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        RE_GenericBean rgb = null;
        RE_GenericBean rgb2 = null;
        DateTime lunes, martes, miercoles, jueves, viernes, sabado, domingo;
        try
        {
            DateTime ahora = DateTime.Now;
            System.Globalization.Calendar c = CultureInfo.CurrentCulture.Calendar;
            int semana = c.GetWeekOfYear(ahora, CalendarWeekRule.FirstDay, ahora.DayOfWeek);

            lunes = Utility.FirstDateOfWeek(ahora.Year, semana, 1);
            martes = Utility.FirstDateOfWeek(ahora.Year, semana, 2);
            miercoles = Utility.FirstDateOfWeek(ahora.Year, semana, 3);
            jueves = Utility.FirstDateOfWeek(ahora.Year, semana, 4);
            viernes = Utility.FirstDateOfWeek(ahora.Year, semana, 5);
            sabado = Utility.FirstDateOfWeek(ahora.Year, semana, 6);
            domingo = Utility.FirstDateOfWeek(ahora.Year, semana, 7);

            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            //comm.CommandText = "select count(*), agente, fecha from (select dat_xml.query('//campos/frmAgentName').value('.', 'varchar(max)') as agente, dat_xml.query('//campos/frmTypeSale').value('.', 'varchar(max)') as tipo, dat_xml.query('//campos/frmQCDate').value('.', 'datetime') as fecha from tbl_datos where (datepart(WEEK,dat_xml.query('//campos/frmQCDate').value('.', 'datetime'))=DATEPART(WEEK,GETDATE())) and dat_xml.query('//campos/frmQCStatus').value('.', 'varchar(max)')='APPROVED') tmp group by agente, fecha";
            comm.CommandText = "select count(*), agente, fecha, tipo from (select dat_xml.query('//campos/frmAgentName').value('.', 'varchar(max)') as agente, dat_xml.query('//campos/frmTypeSale2').value('.', 'varchar(max)') as tipo, dat_xml.query('//campos/frmQCDate').value('.', 'date') as fecha from tbl_datos where (datepart(WEEK,dat_xml.query('//campos/frmQCDate').value('.', 'date'))=DATEPART(WEEK,GETDATE())) and dat_xml.query('//campos/frmQCStatus').value('.', 'varchar(max)')='APPROVED') tmp, tbl_usuario_perfil, tbl_usuario_descripcion where ude_id=upe_ude_id and upe_per_id=3 and ude_id = agente group by agente, tipo, fecha";
            reader = comm.ExecuteReader();

            while (reader.Read())
            {
                rgb = new RE_GenericBean();
                if (!reader.IsDBNull(0)) rgb.intC1 = reader.GetInt32(0);
                if (!reader.IsDBNull(1)) rgb.strC1 = reader.GetString(1);
                if (!reader.IsDBNull(2)) rgb.strC2 = reader.GetDateTime(2).ToShortDateString();
                if (!reader.IsDBNull(3)) rgb.strC3 = reader.GetString(3);

                if (result.ContainsKey(rgb.strC1))
                {
                    rgb2 = (RE_GenericBean)result[rgb.strC1];
                    if (rgb.strC3.Equals("DOUBLE")) rgb.intC1 = rgb.intC1 * 2;
                    if (lunes.ToShortDateString().Equals(rgb.strC2)) rgb2.intC1 += rgb.intC1;
                    if (martes.ToShortDateString().Equals(rgb.strC2)) rgb2.intC2 += rgb.intC1;
                    if (miercoles.ToShortDateString().Equals(rgb.strC2)) rgb2.intC3 += rgb.intC1;
                    if (jueves.ToShortDateString().Equals(rgb.strC2)) rgb2.intC4 += rgb.intC1;
                    if (viernes.ToShortDateString().Equals(rgb.strC2)) rgb2.intC5 += rgb.intC1;
                    if (sabado.ToShortDateString().Equals(rgb.strC2)) rgb2.intC6 += rgb.intC1;
                    if (domingo.ToShortDateString().Equals(rgb.strC2)) rgb2.intC7 += rgb.intC1;
                    result.Remove(rgb.strC1);
                    result.Add(rgb.strC1, rgb2);
                }
                else
                {
                    rgb2 = new RE_GenericBean();
                    if (rgb.strC3.Equals("DOUBLE")) rgb.intC1 = rgb.intC1 * 2;
                    rgb2.strC1 = rgb.strC1;
                    if (lunes.ToShortDateString().Equals(rgb.strC2)) rgb2.intC1 = rgb.intC1;
                    if (martes.ToShortDateString().Equals(rgb.strC2)) rgb2.intC2 = rgb.intC1;
                    if (miercoles.ToShortDateString().Equals(rgb.strC2)) rgb2.intC3 = rgb.intC1;
                    if (jueves.ToShortDateString().Equals(rgb.strC2)) rgb2.intC4 = rgb.intC1;
                    if (viernes.ToShortDateString().Equals(rgb.strC2)) rgb2.intC5 = rgb.intC1;
                    if (sabado.ToShortDateString().Equals(rgb.strC2)) rgb2.intC6 = rgb.intC1;
                    if (domingo.ToShortDateString().Equals(rgb.strC2)) rgb2.intC7 = rgb.intC1;
                    result.Add(rgb.strC1, rgb2);
                }
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static RE_GenericBean getUserData(string userid)
    {
        RE_GenericBean result = new RE_GenericBean();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;


            comm.CommandText = "SELECT * fROM tbl_usuario_descripcion";
            if (!userid.Trim().Equals(""))
            {
                comm.CommandText += " where ude_id=@usuario";
                comm.Parameters.Add("@usuario", SqlDbType.VarChar).Value = userid;
            }
            reader = comm.ExecuteReader();

            if (reader.Read())
            {
                if (!reader.IsDBNull(0)) result.strC1 = reader.GetString(0);
                if (!reader.IsDBNull(1)) result.strC2 = reader.GetString(1);
                if (!reader.IsDBNull(2)) result.strC3 = reader.GetString(2);
                if (!reader.IsDBNull(3)) result.strC4 = reader.GetString(3);
                if (!reader.IsDBNull(4)) result.strC5 = reader.GetString(4);
                result.arr1 = getPerfilesbyUser(userid);
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static int deleteUserData(string userid)
    {
        int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "delete fROM tbl_usuario_descripcion where ude_id=@usuario";
            comm.Parameters.Add("@usuario", SqlDbType.VarChar).Value = userid;
            result = comm.ExecuteNonQuery();

        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return -1;
        }
        finally
        {
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static RE_User ValidaCliente(String user, String pss)
    {
        RE_User result = null;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader reader = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            String sql = "SELECT ude_id, ude_contrasena, ude_nombre, ude_apellido, ude_abreviado, upe_per_id fROM tbl_usuario_descripcion, tbl_usuario_perfil WHERE (upper(ude_id) = '" + user.Trim() + "') AND (ude_contrasena = '" + pss.Trim() + "') and upe_ude_id=ude_id";
            //comm.Parameters.Add("user", SqlDbType.Char, 20).Value = user.Trim();
            //comm.Parameters.Add("contrasena", SqlDbType.VarChar, 32).Value=pss.Trim();
            comm.CommandText = sql;
            reader = comm.ExecuteReader();

            if (reader.Read())
            {
                result = new RE_User();
                result.UserID = reader.GetString(0);
                result.Pss = reader.GetString(1);
                result.Name = reader.GetString(2);
                result.LastName = reader.GetString(3);
                result.Abreviado = reader.GetString(4);
                result.Level = reader.GetInt32(5);
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (reader != null)
                reader.Dispose();
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }
    public static ArrayList getCamposFormulario(string sectionin)
    {
        ArrayList result = null;
        campos_Bean campo = null;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader rs = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "SELECT tic_tca_id, tic_allow_null, tic_valida_expresion, tca_display_name, ttc_regular_expression, ttc_name, tca_max_length, tca_table_dependency, tca_field_dependency, tca_where_dependency, tca_size, tic_message_expresion, tca_selectedindex, tca_rows FROM tbl_campo, tbl_industria_campo, tbl_tipo_campo where tca_id = tic_tca_id and tca_ttc_id=ttc_id";
            if (sectionin != null && !sectionin.Equals(""))
                comm.CommandText += " and tic_tse_id in (" + sectionin + ")";
            comm.CommandText += "order by tic_tse_id, tic_order_insection";


            result = new ArrayList();
            rs = comm.ExecuteReader();
            while (rs.Read())
            {
                campo = new campos_Bean();
                if (!rs.IsDBNull(0)) campo.Nombre = rs.GetString(0);
                if (!rs.IsDBNull(1)) campo.AllowNull = rs.GetInt32(1);
                if (!rs.IsDBNull(2)) campo.ValidaExpr = rs.GetInt32(2);
                if (!rs.IsDBNull(3)) campo.DisplayName = rs.GetString(3);
                if (!rs.IsDBNull(4)) campo.Expresion_Reg = rs.GetString(4);
                if (!rs.IsDBNull(5)) campo.TipoCampo = rs.GetString(5);
                if (!rs.IsDBNull(6)) campo.MaxLen = rs.GetInt32(6);
                if (!rs.IsDBNull(7)) campo.TablaDep = rs.GetString(7);
                if (!rs.IsDBNull(8)) campo.CampoDep = rs.GetString(8);
                if (!rs.IsDBNull(9)) campo.WhereDep = rs.GetString(9);
                if (!rs.IsDBNull(10)) campo.Size = rs.GetInt32(10);
                if (!rs.IsDBNull(11)) campo.MessageError = rs.GetString(11);
                if (!rs.IsDBNull(12)) campo.SelectedIndex = rs.GetInt32(12);
                if (!rs.IsDBNull(13)) campo.Rows = rs.GetInt32(13);
                result.Add(campo);
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (rs != null)
            {
                rs.Dispose();
                rs.Close();
            }
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static ArrayList getListado(string TablaDep, string CampoDep, string WhereDep)
    {
        ArrayList result = null;
        RE_GenericBean rgb = null;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader rs = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "SELECT " + CampoDep + " FROM " + TablaDep + " where " + WhereDep;

            result = new ArrayList();
            rs = comm.ExecuteReader();
            while (rs.Read())
            {
                rgb = new RE_GenericBean();
                if (!rs.IsDBNull(0)) rgb.strC1 = rs.GetString(0);
                if (!rs.IsDBNull(1)) rgb.strC2 = rs.GetString(1);
                result.Add(rgb);
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (rs != null)
            {
                rs.Dispose();
                rs.Close();
            }
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static int InsetUpdateData(int id, string data)
    {
        int result = -1;
        SqlConnection conn = null;
        SqlCommand comm = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(data);
            escritor.Close();

            if (id == 0)
            {
                comm.CommandText = "insert into tbl_datos (dat_usuario, dat_fecha, dat_hora,dat_tco_id, dat_xml) values (@user, @fecha, @hora, @companyID, @xml)";
                comm.Parameters.Add("@user", SqlDbType.VarChar).Value = "System";
                comm.Parameters.Add("@companyID", SqlDbType.Int).Value = 1;
                comm.Parameters.Add("@xml", SqlDbType.VarChar).Value = data;
                comm.Parameters.Add("@fecha", SqlDbType.Date).Value = DateTime.Today.ToShortDateString();
                comm.Parameters.Add("@hora", SqlDbType.VarChar).Value = DateTime.Now.ToShortTimeString();
                
            }
            else
            {
                comm.CommandText = "update tbl_datos set dat_xml=@xml where dat_id=@id";
                comm.Parameters.Add("@xml", SqlDbType.VarChar).Value = data;
                comm.Parameters.Add("@id", SqlDbType.Int).Value = id;
            }

            result = comm.ExecuteNonQuery();
        }
        catch (Exception e)
        {
            Console.WriteLine(DateTime.Now.ToString()+"__"+ e.Message.ToString() + e.StackTrace);
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(DateTime.Now.ToString() + "__" + e.Message.ToString() + e.StackTrace);
            escritor.Close();
            return -100;
        }
        finally
        {
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }


    public static int InsertUpdateUserData(string userid, RE_GenericBean userbean)
    {
        int result = -1;
        SqlConnection conn = null;
        SqlCommand comm = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;

            if (userid.Trim().Equals(""))
            {
                comm.CommandText = "insert into tbl_usuario_descripcion (ude_id, ude_contrasena, ude_nombre, ude_apellido, ude_abreviado) values (@userid, @pass, @nombre, @ape, @corto)";
                comm.Parameters.Add("@userid", SqlDbType.VarChar).Value = userbean.strC1;
                comm.Parameters.Add("@pass", SqlDbType.VarChar).Value = userbean.strC2;
                comm.Parameters.Add("@nombre", SqlDbType.VarChar).Value = userbean.strC3;
                comm.Parameters.Add("@ape", SqlDbType.VarChar).Value = userbean.strC4;
                comm.Parameters.Add("@corto", SqlDbType.VarChar).Value = userbean.strC5;
            }
            else
            {
                comm.CommandText = "update tbl_usuario_descripcion set ude_id=@userid, ude_contrasena=@pass, ude_nombre=@nombre, ude_apellido=@ape, ude_abreviado=@corto where ude_id=@usuarioID";
                comm.Parameters.Add("@usuarioID", SqlDbType.VarChar).Value = userid;
                comm.Parameters.Add("@userid", SqlDbType.VarChar).Value = userbean.strC1;
                comm.Parameters.Add("@pass", SqlDbType.VarChar).Value = userbean.strC2;
                comm.Parameters.Add("@nombre", SqlDbType.VarChar).Value = userbean.strC3;
                comm.Parameters.Add("@ape", SqlDbType.VarChar).Value = userbean.strC4;
                comm.Parameters.Add("@corto", SqlDbType.VarChar).Value = userbean.strC5;
            }
            result = comm.ExecuteNonQuery();
            comm.Parameters.Clear();

            comm.CommandText = "delete from tbl_usuario_perfil where upe_ude_id=@usuarioID";
            comm.Parameters.Add("@usuarioID", SqlDbType.VarChar).Value = userid;
            comm.ExecuteNonQuery();
            comm.Parameters.Clear();
            if (userbean.arr1 != null && userbean.arr1.Count > 0)
            {
                foreach (string perfilid in userbean.arr1)
                {
                    comm.CommandText = "insert into tbl_usuario_perfil (upe_ude_id, upe_per_id, upe_estado) values (@userid, @perfilid, 1)";
                    comm.Parameters.Add("@userid", SqlDbType.VarChar).Value = userbean.strC1;
                    comm.Parameters.Add("@perfilid", SqlDbType.Int).Value = int.Parse(perfilid);
                    comm.ExecuteNonQuery();
                    comm.Parameters.Clear();
                }
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message.ToString());
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return -100;
        }
        finally
        {
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static int getCountbyDay(string tipo)
    {
        int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader rs = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "select COUNT(*) from tbl_datos where datepart(DAYOFYEAR,dat_xml.query('//campos/frmQCDate').value('.', 'datetime'))=DATEPART(DAYOFYEAR,GETDATE()) and dat_xml.query('//campos/frmTypeSale').value('.', 'varchar(max)')=@tipo  and dat_xml.query('//campos/frmQCStatus').value('.', 'varchar(max)')='APPROVED'";
            comm.Parameters.Add("@tipo", SqlDbType.VarChar).Value = tipo;
            rs = comm.ExecuteReader();
            if (rs.Read())
            {
                result = rs.GetInt32(0);
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return 0;
        }
        finally
        {
            if (rs != null)
            {
                rs.Dispose();
                rs.Close();
            }
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }

        return result;
    }

    public static int getCountbyDayMP(string tipo)
    {
        int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader rs = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "select COUNT(*) from tbl_datos where datepart(DAYOFYEAR,dat_xml.query('//campos/frmQCDate').value('.', 'varchar(max)'))=DATEPART(DAYOFYEAR,GETDATE()) and dat_xml.query('//campos/frmTypeSale2').value('.', 'varchar(max)')=@tipo  and dat_xml.query('//campos/frmQCStatus').value('.', 'varchar(max)')='APPROVED'";
            comm.Parameters.Add("@tipo", SqlDbType.VarChar).Value = tipo;
            rs = comm.ExecuteReader();
            if (rs.Read())
            {
                result = rs.GetInt32(0);
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return 0;
        }
        finally
        {
            if (rs != null)
            {
                rs.Dispose();
                rs.Close();
            }
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }

        return result;
    }

    public static int getCountbyWeek(string tipo)
    {
        int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader rs = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "select COUNT(*) from tbl_datos where datepart(WEEK,dat_xml.query('//campos/frmQCDate').value('.', 'varchar(max)'))=DATEPART(WEEK,GETDATE()) and dat_xml.query('//campos/frmTypeSale').value('.', 'varchar(max)')=@tipo  and dat_xml.query('//campos/frmQCStatus').value('.', 'varchar(max)')='APPROVED'";
            comm.Parameters.Add("@tipo", SqlDbType.VarChar).Value = tipo;
            rs = comm.ExecuteReader();
            if (rs.Read())
            {
                result = rs.GetInt32(0);
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return 0;
        }
        finally
        {
            if (rs != null)
            {
                rs.Dispose();
                rs.Close();
            }
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }

        return result;
    }

    public static int getCountbyWeekMP(string tipo)
    {
        int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader rs = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "select COUNT(*) from tbl_datos where datepart(WEEK,dat_xml.query('//campos/frmQCDate').value('.', 'varchar(max)'))=DATEPART(WEEK,GETDATE()) and dat_xml.query('//campos/frmTypeSale2').value('.', 'varchar(max)')=@tipo  and dat_xml.query('//campos/frmQCStatus').value('.', 'varchar(max)')='APPROVED'";
            comm.Parameters.Add("@tipo", SqlDbType.VarChar).Value = tipo;
            rs = comm.ExecuteReader();
            if (rs.Read())
            {
                result = rs.GetInt32(0);
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return 0;
        }
        finally
        {
            if (rs != null)
            {
                rs.Dispose();
                rs.Close();
            }
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }

        return result;
    }


    public static int getTotalApproved(string tipo)
    {
        int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader rs = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "select COUNT(*) from tbl_datos where dat_xml.query('//campos/frmTypeSale').value('.', 'varchar(max)')=@tipo  and dat_xml.query('//campos/frmQCStatus').value('.', 'varchar(max)')='APPROVED'";
            comm.Parameters.Add("@tipo", SqlDbType.VarChar).Value = tipo;
            rs = comm.ExecuteReader();
            if (rs.Read())
            {
                result = rs.GetInt32(0);
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return 0;
        }
        finally
        {
            if (rs != null)
            {
                rs.Dispose();
                rs.Close();
            }
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }

        return result;
    }
    public static int getTotalApprovedMP(string tipo)
    {
        int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader rs = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "select COUNT(*) from tbl_datos where (CAST(dat_xml AS VARCHAR(8000)) LIKE '%<client>MPowerEnergy</client>%') and dat_xml.query('//campos/frmTypeSale2').value('.', 'varchar(max)')=@tipo  and dat_xml.query('//campos/frmQCStatus').value('.', 'varchar(max)')='APPROVED'";
            comm.Parameters.Add("@tipo", SqlDbType.VarChar).Value = tipo;
            rs = comm.ExecuteReader();
            if (rs.Read())
            {
                result = rs.GetInt32(0);
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return 0;
        }
        finally
        {
            if (rs != null)
            {
                rs.Dispose();
                rs.Close();
            }
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }

        return result;
    }

    public static DataSet getData(string where)
    {
        DataSet result = new DataSet();
        result.Tables.Add("datos");
        result.Tables["datos"].Columns.Add("ID");
        result.Tables["datos"].Columns.Add("Agent");
        result.Tables["datos"].Columns.Add("Date");
        result.Tables["datos"].Columns.Add("Time");
        result.Tables["datos"].Columns.Add("First Name");
        result.Tables["datos"].Columns.Add("Last Name");
        result.Tables["datos"].Columns.Add("City");
        result.Tables["datos"].Columns.Add("State");
        result.Tables["datos"].Columns.Add("Phone #");
        result.Tables["datos"].Columns.Add("Sale");
        result.Tables["datos"].Columns.Add("Status");
        RE_GenericBean rgb = null;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader rs = null;
        string xml = "";
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            if (where != null && !where.Trim().Equals(""))
            {
                comm.CommandText = "SELECT dat_id, dat_xml from tbl_datos where " + where + " order by dat_xml.query('//campos/frmDateIn').value('.', 'datetime') desc, dat_xml.query('//campos/frmAppTime').value('.', 'datetime') desc";
            }
            else
            {
                comm.CommandText = "SELECT dat_id, dat_xml from tbl_datos order by dat_xml.query('//campos/frmDateIn').value('.', 'datetime') desc, dat_xml.query('//campos/frmAppTime').value('.', 'datetime') desc";
            }
            rs = comm.ExecuteReader();
            while (rs.Read())
            {
                rgb = new RE_GenericBean();
                if (!rs.IsDBNull(1))
                {
                    xml = rs.GetString(1);
                    rgb = PharseXML(xml);
                }
                if (!rs.IsDBNull(0)) rgb.intC10 = rs.GetInt32(0);
                object[] objArr = { rgb.intC10, rgb.strC1, rgb.strC2, rgb.strC3, rgb.strC4, rgb.strC5, rgb.strC6, rgb.strC7, rgb.strC8, rgb.strC9, rgb.strC11 };
                result.Tables["datos"].Rows.Add(objArr);

            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (rs != null)
            {
                rs.Dispose();
                rs.Close();
            }
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }

        return result;
    }
    public static DataSet getDataMPower(string where)
    {
        DataSet result = new DataSet();
        result.Tables.Add("datos");
        result.Tables["datos"].Columns.Add("ID");
        result.Tables["datos"].Columns.Add("AgentName");
        result.Tables["datos"].Columns.Add("Date");
        result.Tables["datos"].Columns.Add("Time");
        result.Tables["datos"].Columns.Add("First Name");
        result.Tables["datos"].Columns.Add("Last Name");
        result.Tables["datos"].Columns.Add("City");
        result.Tables["datos"].Columns.Add("State");
        result.Tables["datos"].Columns.Add("Phone #");
        result.Tables["datos"].Columns.Add("Sale");
        result.Tables["datos"].Columns.Add("Status");
        RE_GenericBean rgb = null;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader rs = null;
        string xml = "";
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            if (where != null && !where.Trim().Equals(""))
            {
                comm.CommandText = "SELECT dat_id, dat_xml from tbl_datos where " + where + " order by dat_xml.query('//campos/frmDateIn').value('.', 'date') desc, dat_xml.query('//campos/frmAppTime').value('.', 'time') desc";
            }
            else
            {
                comm.CommandText = "SELECT dat_id, dat_xml from tbl_datos order by dat_xml.query('//campos/frmDateIn').value('.', 'date') desc, dat_xml.query('//campos/frmAppTime').value('.', 'time') desc";
            }
            rs = comm.ExecuteReader();
            while (rs.Read())
            {
                rgb = new RE_GenericBean();
                if (!rs.IsDBNull(1))
                {
                    xml = rs.GetString(1);
                    rgb = PharseXMLMPower(xml);
                }
                if (!rs.IsDBNull(0)) rgb.intC10 = rs.GetInt32(0);
                object[] objArr = { rgb.intC10, rgb.strC1, rgb.strC3, rgb.strC4, rgb.strC5, rgb.strC6, rgb.strC7, rgb.strC8, rgb.strC9, rgb.strC10, rgb.strC11 };
                result.Tables["datos"].Rows.Add(objArr);

            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (rs != null)
            {
                rs.Dispose();
                rs.Close();
            }
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }

        return result;
    }

    public static Hashtable getDatabyID(int id)
    {
        Hashtable result = null;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader rs = null;
        string xml = "";
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "SELECT dat_id, dat_xml from tbl_datos where dat_id=@id";
            comm.Parameters.Add("@id", SqlDbType.Int).Value = id;
            rs = comm.ExecuteReader();
            while (rs.Read())
            {
                result = new Hashtable();
                if (!rs.IsDBNull(1))
                {
                    xml = rs.GetString(1);
                    result = HashPharseXML(xml);
                }
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (rs != null)
            {
                rs.Dispose();
                rs.Close();
            }
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }

        return result;
    }

    private static RE_GenericBean PharseXML(string xml)
    {

        RE_GenericBean rgb = new RE_GenericBean();
        try
        {
            XmlDocument Xdoc = new XmlDocument();
            Xdoc.Load(new StringReader(xml));
            XmlElement root = Xdoc.DocumentElement;
            XmlNodeList campos = root.ChildNodes;
            foreach (XmlElement campo in campos)
            {
                if (campo.Name.Equals("frmAgentName")) rgb.strC1 = campo.InnerText;
                if (campo.Name.Equals("frmDateIn")) rgb.strC2 = campo.InnerText;
                if (campo.Name.Equals("frmAppTime")) rgb.strC3 = campo.InnerText;
                if (campo.Name.Equals("frmFirstName")) rgb.strC4 = campo.InnerText;
                if (campo.Name.Equals("frmLastName")) rgb.strC5 = campo.InnerText;
                if (campo.Name.Equals("frmCity")) rgb.strC6 = campo.InnerText;
                if (campo.Name.Equals("frmState")) rgb.strC7 = campo.InnerText;
                if (campo.Name.Equals("frmPhontNumber")) rgb.strC8 = campo.InnerText;
                if (campo.Name.Equals("frmTypeSale")) rgb.strC9 = campo.InnerText;
                if (campo.Name.Equals("frmQCStatus")) rgb.strC11 = campo.InnerText;

            }
            if (rgb.strC2 != null && !rgb.strC2.Equals("")) rgb.strC2 = rgb.strC2.Substring(0, 5);
            if (rgb.strC12 != null && !rgb.strC12.Equals("")) rgb.strC12 = rgb.strC12.Substring(0, 5);
            if (rgb.strC11 == null || rgb.strC11.Equals("")) rgb.strC11 = "PENDING";

        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        return rgb;
    }

    private static RE_GenericBean PharseXMLMPower(string xml)
    {

        RE_GenericBean rgb = new RE_GenericBean();
        try
        {
            XmlDocument Xdoc = new XmlDocument();
            Xdoc.Load(new StringReader(xml));
            XmlElement root = Xdoc.DocumentElement;
            XmlNodeList campos = root.ChildNodes;
            foreach (XmlElement campo in campos)
            {
                if (campo.Name.Equals("frmAgentId")) rgb.strC1 = campo.InnerText;
                if (campo.Name.Equals("frmAgentName")) rgb.strC2 = campo.InnerText;
                if (campo.Name.Equals("frmDateIn")) rgb.strC3 = campo.InnerText;
                if (campo.Name.Equals("frmAppTime")) rgb.strC4 = campo.InnerText;
                if (campo.Name.Equals("frmFirstName")) rgb.strC5 = campo.InnerText;
                if (campo.Name.Equals("frmLastName")) rgb.strC6 = campo.InnerText;
                if (campo.Name.Equals("frmCity")) rgb.strC7 = campo.InnerText;
                if (campo.Name.Equals("frmState")) rgb.strC8 = campo.InnerText;
                if (campo.Name.Equals("frmPhontNumber")) rgb.strC9 = campo.InnerText;
                if (campo.Name.Equals("frmTypeSale2")) rgb.strC10 = campo.InnerText;
                if (campo.Name.Equals("frmQCStatus")) rgb.strC11 = campo.InnerText;

            }
            if (rgb.strC3 != null && !rgb.strC3.Equals("")) rgb.strC3 = rgb.strC3.Substring(0, 5);
            if (rgb.strC11 == null || rgb.strC11.Equals("")) rgb.strC11 = "PENDING";

        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        return rgb;
    }

    private static Hashtable HashPharseXML(string xml)
    {

        Hashtable ht = new Hashtable();
        try
        {
            XmlDocument Xdoc = new XmlDocument();
            Xdoc.Load(new StringReader(xml));
            XmlElement root = Xdoc.DocumentElement;
            XmlNodeList campos = root.ChildNodes;
            foreach (XmlElement campo in campos)
            {
                if (!ht.ContainsKey(campo.Name.ToString()))
                {
                    ht.Add(campo.Name.ToString(), campo.InnerText.ToString().ToUpper());
                }
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        return ht;
    }

    public static ArrayList getSections(int tipoindustria)
    {
        ArrayList result = null;
        RE_GenericBean campo = null;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader rs = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "select tse_id, tse_display_name, tse_cols_number from tbl_section where tse_tin_id=@tipoindustria";
            comm.Parameters.Add("@tipoindustria", SqlDbType.Int).Value = tipoindustria;
            result = new ArrayList();
            rs = comm.ExecuteReader();
            while (rs.Read())
            {
                campo = new RE_GenericBean();
                if (!rs.IsDBNull(0)) campo.intC1 = rs.GetInt32(0);
                if (!rs.IsDBNull(1)) campo.strC1 = rs.GetString(1);
                if (!rs.IsDBNull(2)) campo.intC2 = rs.GetInt32(2);
                result.Add(campo);
            }
        }
        catch (Exception e)
        {
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (rs != null)
            {
                rs.Dispose();
                rs.Close();
            }
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }
    public static int deleteData(int id)
    {
        int result = -1;
        SqlConnection conn = null;
        SqlCommand comm = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;

            if (id != 0)
            {
                comm.CommandText = "delete from tbl_datos where dat_id=@id";
                comm.Parameters.Add("@id", SqlDbType.Int).Value = id;
            }

            result = comm.ExecuteNonQuery();
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message.ToString());
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return -100;
        }
        finally
        {
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static int InsertGoal(int goal, string userID)
    {
        int result = -1;
        SqlConnection conn = null;
        SqlCommand comm = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;

            comm.CommandText = "delete from tbl_agent_goal where DATEPART(WEEK,GETDATE())=DATEPART(WEEK,goa_date) and goa_ude_id=@agent";
            comm.Parameters.Add("@agent", SqlDbType.VarChar).Value = userID;
            result = comm.ExecuteNonQuery();
            comm.Parameters.Clear();

            comm.CommandText = "insert into tbl_agent_goal (goa_ude_id, goa_date, goa_goal) values (@agent, GETDATE(), @goal)";
            comm.Parameters.Add("@agent", SqlDbType.VarChar).Value = userID;
            comm.Parameters.Add("@goal", SqlDbType.Int).Value = goal;
            result = comm.ExecuteNonQuery();
            comm.Parameters.Clear();
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message.ToString());
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return -100;
        }
        finally
        {
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static DataTable getGoals(string userID)
    {
        DataTable dt = new DataTable("goals");
        dt.Columns.Add("Agent Name");
        dt.Columns.Add("Date");
        dt.Columns.Add("Goal");
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader rs = null;
        RE_GenericBean rgb = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;

            comm.CommandText = "select ude_nombre + ' ' + ude_apellido , goa_date, goa_goal from tbl_agent_goal, tbl_usuario_descripcion where ude_id=goa_ude_id and ude_id=@agent";
            comm.Parameters.Add("@agent", SqlDbType.VarChar).Value = userID;
            rs = comm.ExecuteReader();
            while (rs.Read())
            {
                rgb = new RE_GenericBean();
                if (!rs.IsDBNull(0)) rgb.strC1 = rs.GetString(0);
                if (!rs.IsDBNull(1)) rgb.strC2 = rs.GetDateTime(1).ToShortDateString();
                if (!rs.IsDBNull(2)) rgb.intC1 = rs.GetInt32(2);
                object[] objArr = { rgb.strC1, rgb.strC2, rgb.intC1 };
                dt.Rows.Add(objArr);
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message.ToString());
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return dt;
    }

    public static ArrayList getPerfilesbyUser(string userID)
    {
        ArrayList result = null;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader rs = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;

            comm.CommandText = "select upe_per_id from tbl_usuario_perfil where upe_ude_id=@agent";
            comm.Parameters.Add("@agent", SqlDbType.VarChar).Value = userID;
            rs = comm.ExecuteReader();
            while (rs.Read())
            {
                if (result == null) result = new ArrayList();
                if (!rs.IsDBNull(0)) result.Add(rs.GetInt32(0).ToString());
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message.ToString());
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static ArrayList getPerfiles()
    {
        ArrayList result = new ArrayList();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader rs = null;
        RE_GenericBean rgb = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;

            comm.CommandText = "select per_id, per_nombre from tbl_perfil";
            rs = comm.ExecuteReader();
            while (rs.Read())
            {
                rgb = new RE_GenericBean();
                if (!rs.IsDBNull(0)) rgb.intC1 = rs.GetInt32(0);
                if (!rs.IsDBNull(1)) rgb.strC1 = rs.GetString(1);
                result.Add(rgb);
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message.ToString());
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static int getCurrentGoal(string user)
    {
        int result = 0;
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader rs = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;

            comm.CommandText = "select goa_goal from tbl_Agent_goal where goa_ude_id=(select ude_id from tbl_usuario_descripcion where ude_nombre + ' ' + ude_apellido=@agentID)";
            comm.Parameters.Add("@agentID", SqlDbType.VarChar).Value = user;
            rs = comm.ExecuteReader();
            if (rs.Read())
            {
                if (!rs.IsDBNull(0)) result = rs.GetInt32(0);
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message.ToString());
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return -100;
        }
        finally
        {
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }
    public static ReportDS getAgentData(string where)
    {
        ReportDS ds = new ReportDS();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader rs = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;

            comm.CommandText = "select ";
            comm.CommandText += "dat_xml.query('//campos/frmAgentName').value('.', 'varchar(max)') as agente, ";
            comm.CommandText += "dat_xml.query('//campos/frmDateIn').value('.', 'datetime') as fecha, ";
            comm.CommandText += "dat_xml.query('//campos/frmAppTime').value('.', 'datetime') as tiempo, ";
            comm.CommandText += "dat_xml.query('//campos/frmFirstName').value('.', 'varchar(max)') as nombre, ";
            comm.CommandText += "dat_xml.query('//campos/frmLastName').value('.', 'varchar(max)') as apellido, ";
            comm.CommandText += "dat_xml.query('//campos/frmCity').value('.', 'varchar(max)') as city, ";
            comm.CommandText += "dat_xml.query('//campos/frmState').value('.', 'varchar(max)') as state, ";
            comm.CommandText += "dat_xml.query('//campos/frmTypeSale').value('.', 'varchar(max)') as tipo ";
            comm.CommandText += "from tbl_datos where " + where;
            rs = comm.ExecuteReader();

            RE_GenericBean rgb = null;
            while (rs.Read())
            {
                rgb = new RE_GenericBean();
                if (!rs.IsDBNull(0)) rgb.strC1 = rs.GetString(0);
                if (!rs.IsDBNull(1)) rgb.strC2 = rs.GetDateTime(1).ToShortDateString();
                if (!rs.IsDBNull(2)) rgb.strC3 = rs.GetDateTime(2).ToShortTimeString();
                if (!rs.IsDBNull(3)) rgb.strC4 = rs.GetString(3);
                if (!rs.IsDBNull(4)) rgb.strC5 = rs.GetString(4);
                if (!rs.IsDBNull(5)) rgb.strC6 = rs.GetString(5);
                if (!rs.IsDBNull(6)) rgb.strC7 = rs.GetString(6);
                if (!rs.IsDBNull(7)) rgb.strC8 = rs.GetString(7);

                ds.tbl_agent.Addtbl_agentRow(rgb.strC1, DateTime.Parse(rgb.strC2), DateTime.Parse(rgb.strC3), rgb.strC4, rgb.strC5, rgb.strC6, rgb.strC7, rgb.strC8);

            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message.ToString());
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return ds;
    }

    public static ReportDS getAgentDataMP(string where)
    {
        ReportDS ds = new ReportDS();
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader rs = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;

            comm.CommandText = "select ";
            comm.CommandText += "dat_xml.query('//campos/frmAgentName').value('.', 'varchar(max)') as agente, ";
            comm.CommandText += "dat_xml.query('//campos/frmDateIn').value('.', 'datetime') as fecha, ";
            comm.CommandText += "dat_xml.query('//campos/frmAppTime').value('.', 'datetime') as tiempo, ";
            comm.CommandText += "dat_xml.query('//campos/frmFirstName').value('.', 'varchar(max)') as nombre, ";
            comm.CommandText += "dat_xml.query('//campos/frmLastName').value('.', 'varchar(max)') as apellido, ";
            comm.CommandText += "dat_xml.query('//campos/frmCity').value('.', 'varchar(max)') as city, ";
            comm.CommandText += "dat_xml.query('//campos/frmState').value('.', 'varchar(max)') as state, ";
            comm.CommandText += "dat_xml.query('//campos/frmTypeSale2').value('.', 'varchar(max)') as tipo ";
            comm.CommandText += "from tbl_datos where " + where;
            rs = comm.ExecuteReader();

            RE_GenericBean rgb = null;
            while (rs.Read())
            {
                rgb = new RE_GenericBean();
                if (!rs.IsDBNull(0)) rgb.strC1 = rs.GetString(0);
                if (!rs.IsDBNull(1)) rgb.strC2 = rs.GetDateTime(1).ToShortDateString();
                if (!rs.IsDBNull(2)) rgb.strC3 = rs.GetDateTime(2).ToShortTimeString();
                if (!rs.IsDBNull(3)) rgb.strC4 = rs.GetString(3);
                if (!rs.IsDBNull(4)) rgb.strC5 = rs.GetString(4);
                if (!rs.IsDBNull(5)) rgb.strC6 = rs.GetString(5);
                if (!rs.IsDBNull(6)) rgb.strC7 = rs.GetString(6);
                if (!rs.IsDBNull(7)) rgb.strC8 = rs.GetString(7);

                ds.tbl_agent.Addtbl_agentRow(rgb.strC1, DateTime.Parse(rgb.strC2), DateTime.Parse(rgb.strC3), rgb.strC4, rgb.strC5, rgb.strC6, rgb.strC7, rgb.strC8);

            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message.ToString());
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return ds;
    }
      
     //&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
    public static DataTable getDatosPatriota(string where)
    {
        DataTable dt = new DataTable("patriota");
        dt.Columns.Add("padron");
        dt.Columns.Add("nombre");
        dt.Columns.Add("cedula");
        dt.Columns.Add("cui");
        dt.Columns.Add("centro");
        dt.Columns.Add("estado");
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader rs = null;
        RE_GenericBean rgb = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;

            comm.CommandText = "select padron, nombre, cedula, cui,  cv_id,estado from tbl_patriota where padron<>0 " + where ;
            rs = comm.ExecuteReader();
            while (rs.Read())
            {
                rgb = new RE_GenericBean();
                if (!rs.IsDBNull(0)) rgb.intC1 = rs.GetInt32(0);
                if (!rs.IsDBNull(1)) rgb.strC1 = rs.GetString(1);
                if (!rs.IsDBNull(2)) rgb.strC2 = rs.GetString(2);
                if (!rs.IsDBNull(3)) rgb.strC3= rs.GetString(3);
                if (!rs.IsDBNull(4)) rgb.strC4 = rs.GetString(4);
                if (!rs.IsDBNull(5)) rgb.intC3 = rs.GetInt32(5);
                object[] objArr = { rgb.intC1, rgb.strC1, rgb.strC2,  rgb.strC3,  rgb.strC4, rgb.intC3 };
                dt.Rows.Add(objArr);
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message.ToString());
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return dt;
    }

    public static int updateEstadoPatriota(int padron, int estado)
    {
        int result = -1;
        SqlConnection conn = null;
        SqlCommand comm = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;

            comm.CommandText = "update tbl_patriota set Estado=@estado where Padron=@padron";
            comm.Parameters.Add("@padron", SqlDbType.Int).Value = padron;
            comm.Parameters.Add("@estado", SqlDbType.Int).Value = estado;
            result = comm.ExecuteNonQuery();
            comm.Parameters.Clear();
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message.ToString());
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return -100;
        }
        finally
        {
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static int updateEstadoPatriota( string where, int estado, string centro, string usuarioid)
    {
        
        int result = -1;
        SqlConnection conn = null;
        SqlCommand comm = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
           
            if (estado == 1)
            {
                where += "and (cv_id='' or cv_id=null)";
            }
            comm.CommandText = "update tbl_patriota set Estado=@estado, cv_id=@centro , usuario= @usuario " + where;
 
            comm.Parameters.Add("@estado", SqlDbType.Int).Value = estado;
            comm.Parameters.Add("@centro", SqlDbType.VarChar).Value = centro;
            comm.Parameters.Add("@usuario", SqlDbType.VarChar).Value = usuarioid;
            result = comm.ExecuteNonQuery();
            comm.Parameters.Clear();
            result = 1;
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message.ToString());
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            result= 0;
        }
        finally
        {
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

    public static int getVotosPatriota(string where)
    {
        int result = -1;
        SqlConnection conn = null;
        SqlCommand comm = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;

            comm.CommandText = "select count(*) from tbl_patriota " + where;
            result = int.Parse(comm.ExecuteScalar().ToString());
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message.ToString());
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return -100;
        }
        finally
        {
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }
    //  obteniendo los centros de votacion
    public static DataTable getCentrosVotacion()
    {
        DataTable dt = new DataTable("centros");
        dt.Columns.Add("cv_id");
        dt.Columns.Add("cv_id2");
        dt.Columns.Add("nombre");
        dt.Columns.Add("direccion");
        dt.Columns.Add("codigonombre");
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader rs = null;
        RE_GenericBean rgb = null;
        try
        {



            conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=PacificStarBPO;Integrated Security=True;Pooling=False");
            conn.Open();


            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;

            comm.CommandText = "SELECT DISTINCT  CAST(SUBSTRING(cv_id, 3, 6) AS INT) AS Expr3, cv_id, UPPER(nombre) AS Expr1, direccion, cv_id + '- ' + SUBSTRING(UPPER(nombre),1,65) AS Expr2 FROM   tbl_centrovotacion ORDER BY Expr3";
            rs = comm.ExecuteReader();
            rgb = new RE_GenericBean();
            rgb.intC1 = 0;
            rgb.strC1 = "Elegir"; rgb.strC2 = "Elegir"; rgb.strC3 = "Elegir"; rgb.strC4 = "Elegir";
            object[] objArr1 = { rgb.intC1, rgb.strC1, rgb.strC2, rgb.strC3, rgb.strC4 };
            dt.Rows.Add(objArr1);
            while (rs.Read())
            {
                rgb = new RE_GenericBean();
                if (!rs.IsDBNull(0)) rgb.intC1 = rs.GetInt32(0);
                if (!rs.IsDBNull(1)) rgb.strC2 = rs.GetString(1);
                if (!rs.IsDBNull(2)) rgb.strC3 = rs.GetString(2);
                if (!rs.IsDBNull(3)) rgb.strC4 = rs.GetString(3);
                if (!rs.IsDBNull(4)) rgb.strC5 = rs.GetString(4);


                object[] objArr = { rgb.intC1, rgb.strC2, rgb.strC3, rgb.strC4, rgb.strC5 };
                dt.Rows.Add(objArr);
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message.ToString());
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return dt;
    }

    public static int updateCentroUserPatriota(int padron, int centro)
    {
        int result = -1;
        SqlConnection conn = null;
        SqlCommand comm = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;

            comm.CommandText = "update tbl_patriota set cv_id=@centro where Padron=@padron";
            comm.Parameters.Add("@padron", SqlDbType.Int).Value = padron;
            comm.Parameters.Add("@centro", SqlDbType.Int).Value = centro;
            result = comm.ExecuteNonQuery();
            comm.Parameters.Clear();
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message.ToString());
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return -100;
        }
        finally
        {
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
    }

   

    public static int addUserPatriota(string padron, string nombre, string apellido, string cedula, string dpi, string muniac, string fechanac, string centro, string deptonac, string genero, string lee, string municipalidad, string municipio, int voto, string usuario)
    {//addUserPatriota(tb_padron.Text, tb_nombre.Text, tb_apellido.Text, tb_cedula.Text, tb_dpi.Text, tb_muninac.Text, tb_fechanac.Text, cb_centro.SelectedItem.ToString(), cb_deptonac.SelectedItem.ToString(), cb_genero.SelectedItem.ToString(), cb_lee.SelectedItem.ToString(), cb_muni.SelectedIndex.ToString(), cb_municipio.SelectedIndex.ToString());
        int result = -1;
        SqlConnection conn = null;
        SqlCommand comm = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;

           
            comm.CommandText = "insert into tbl_patriota (padron, nombre, cedula, municipio, estado, municipalidad, cui, fecha_nac, depto_nac, muni_nac, genero, lee, cv_id, usuario) values (@padron,@nombre, @cedula, @municipio, @estado, @municipalidad, @cui, @fechanac, @deptonac,@muninac, @genero, @lee, @cv_id, @usuario)";
            comm.Parameters.Add("@padron", SqlDbType.Int).Value = padron;
            comm.Parameters.Add("@nombre", SqlDbType.VarChar).Value = apellido+" , "+ nombre;
            comm.Parameters.Add("@cedula", SqlDbType.VarChar).Value = cedula;
            comm.Parameters.Add("@municipio", SqlDbType.VarChar).Value = municipio;
            comm.Parameters.Add("@estado", SqlDbType.Int).Value = voto;
            comm.Parameters.Add("@municipalidad", SqlDbType.VarChar).Value = municipalidad;
            comm.Parameters.Add("@cui", SqlDbType.VarChar).Value = dpi;
            comm.Parameters.Add("@fechanac", SqlDbType.VarChar).Value = fechanac;
            comm.Parameters.Add("@deptonac", SqlDbType.VarChar).Value = deptonac;
            comm.Parameters.Add("@muninac", SqlDbType.VarChar).Value = muniac;
            comm.Parameters.Add("@genero", SqlDbType.VarChar).Value = genero;
            comm.Parameters.Add("@lee", SqlDbType.VarChar).Value = lee;
            comm.Parameters.Add("@cv_id", SqlDbType.VarChar).Value = centro;
            comm.Parameters.Add("@usuario", SqlDbType.VarChar).Value = usuario;

            result = comm.ExecuteNonQuery();
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine("Inserto User");
            escritor.Close();
            comm.Parameters.Clear();
            result = 1;
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message.ToString());
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.StackTrace+" "+comm.CommandText);
            escritor.Close();
            result = 0;
        }
        finally
        {
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return result;
        
    }

    public static int  sendVotosporCentro() {
     
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader rs = null;
        RE_GenericBean rgb;
        int result=0;
            try
            {

                conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=PacificStarBPO;Integrated Security=True;Pooling=False");
                conn.Open();
                comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandType = CommandType.Text;

                comm.CommandText = "SELECT     cv_id, COUNT(Padron) AS Conteo FROM   tbl_patriota WHERE     (cv_id IN  (SELECT cv_id FROM          tbl_centrovotacion AS tv)) GROUP BY cv_id UNION SELECT DISTINCT cv_id, '0' AS Conteo FROM   tbl_centrovotacion AS tpc WHERE     (cv_id NOT IN (SELECT DISTINCT cv_id FROM tbl_patriota AS tbl_patriota_1)) GROUP BY cv_id ORDER BY cv_id ";
                rs = comm.ExecuteReader();
                string xml = "";
                xml= "<?xml version=\"1.0\" encoding=\"utf-8\"?><centros>";
              
                while (rs.Read())
                {
                    
                    if (!rs.IsDBNull(0)) { xml= xml+ "<centro><codigo> "+ rs.GetString(0)+ "</codigo>"; }
                    if (!rs.IsDBNull(1)) { xml=xml+ "<conteo> "+ rs.GetInt32(1)+" </conteo></centro>"; }
                                        
                }
                xml = xml+ "</centros>";

                string fileName = "c:\\TEMP\\pp.xml";
                FileStream stream = new FileStream(fileName, FileMode.Create, FileAccess.Write);
                StreamWriter writer = new StreamWriter(stream);

                writer.WriteLine(xml);
                writer.Close();


                int resp = Utility.UploadFTP("c:\\TEMP\\pp.xml", "ftp://190.115.1.122/", "pacific", "star");
                if (resp==1){ result=1;
                string ruta = "c:\\TEMP\\archivo.txt";
                StreamWriter escritor = new StreamWriter(ruta, true);
                escritor.WriteLine("mensaje enviado a ftp "+DateTime.Now);
                escritor.Close();
                }else{result=0;}
                
                
              
            } catch(Exception ex){
                
                result=0;
            }
           
               
        return result;
        
    }

    public static DataTable getVotosporCentro()
    {
        DataTable dt = new DataTable("centros");
        dt.Columns.Add("cv_id");
        dt.Columns.Add("nombre");
        dt.Columns.Add("conteo");
        
       
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader rs = null;
        RE_GenericBean rgb;
        int result = 0;
        try
        {

            conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=PacificStarBPO;Integrated Security=True;Pooling=False");
            conn.Open();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;

            comm.CommandText = "SELECT tbl_patriota.cv_id, CAST(SUBSTRING(tbl_patriota.cv_id, 3, 6) AS INT) AS Expr3, COUNT(tbl_patriota.Padron) AS Conteo  FROM   tbl_patriota  WHERE     (tbl_patriota.cv_id IN  (SELECT  cv_id  FROM  tbl_centrovotacion AS tv)) GROUP BY tbl_patriota.cv_id UNION SELECT DISTINCT cv_id, CAST(SUBSTRING(cv_id, 3, 6) AS INT) AS Expr3, '0' AS Conteo FROM   tbl_centrovotacion AS tpc WHERE   (cv_id NOT IN  (SELECT DISTINCT cv_id  FROM  tbl_patriota AS tbl_patriota_1)) GROUP BY cv_id, nombre ORDER BY Conteo DESC, Expr3";
            rs = comm.ExecuteReader();
           
            while (rs.Read())
            {

                rgb = new RE_GenericBean();
                if (!rs.IsDBNull(0)) {  rgb.strC1= rs.GetString(0); }
                if (!rs.IsDBNull(1)) { rgb.intC1= rs.GetInt32(1); }
                if (!rs.IsDBNull(2)) { rgb.intC2 = rs.GetInt32(2); }
                object[] objArr = { rgb.strC1, rgb.intC1, rgb.intC2};
                dt.Rows.Add(objArr);

            }
            

            
           



        }
        catch (Exception ex)
        {

            result = 0;
        }
        finally
        {
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        

        return dt;

    }

    public static DataTable getDatosPatriotaOrdenados(string[] where, int cont)
    {
        DataTable dt = new DataTable("patriota");
        dt.Columns.Add("padron");
        dt.Columns.Add("nombre");
        dt.Columns.Add("cedula");
        dt.Columns.Add("cui");
        dt.Columns.Add("centro");
        dt.Columns.Add("estado");
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader rs = null;
        RE_GenericBean rgb = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            for  (int i=0; i<cont; i++){
            comm.CommandText = "select padron, nombre, cedula, cui,  cv_id,estado from tbl_patriota where padron<>0 " + where[i];
            rs = comm.ExecuteReader();
            while (rs.Read())
            {
                rgb = new RE_GenericBean();
                if (!rs.IsDBNull(0)) rgb.intC1 = rs.GetInt32(0);
                if (!rs.IsDBNull(1)) rgb.strC1 = rs.GetString(1);
                if (!rs.IsDBNull(2)) rgb.strC2 = rs.GetString(2);
                if (!rs.IsDBNull(3)) rgb.strC3 = rs.GetString(3);
                if (!rs.IsDBNull(4)) rgb.strC4 = rs.GetString(4);
                if (!rs.IsDBNull(5)) rgb.intC3 = rs.GetInt32(5);
                object[] objArr = { rgb.intC1, rgb.strC1, rgb.strC2, rgb.strC3, rgb.strC4, rgb.intC3 };
                dt.Rows.Add(objArr);
            }
            rs.Close();

        }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message.ToString());
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return dt;
    }
    public static DataTable getDepartamentosGT()
    {
        DataTable dt = new DataTable("departamentos");
        dt.Columns.Add("nombre_dep");
        dt.Columns.Add("id_dep");
        dt.Columns.Add("lideres_dept");
        dt.Columns.Add("meta_dep");
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader rs = null;
        RE_GenericBean rgb = null;
        try
        {



            conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=PacificStarBPO;Integrated Security=True;Pooling=False");
            conn.Open();


            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;

            comm.CommandText = "SELECT nombre_dep, id_dep, lideres_dep, meta_dep FROM departamento ORDER BY nombre_dep";
            rs = comm.ExecuteReader();
            rgb = new RE_GenericBean();
            while (rs.Read())
            {
                rgb = new RE_GenericBean();
                if (!rs.IsDBNull(0)) rgb.strC1 = rs.GetString(0);
                if (!rs.IsDBNull(1)) rgb.intC1 = rs.GetInt32(1);
                if (!rs.IsDBNull(2)) rgb.intC2 = rs.GetInt32(2);
                if (!rs.IsDBNull(3)) rgb.intC3 = rs.GetInt32(3);

                object[] objArr = { rgb.strC1, rgb.intC1, rgb.intC2, rgb.intC3 };
                dt.Rows.Add(objArr);
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message.ToString());
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return dt;
    }

    public static DataTable getMunicipiosGT(int id_dep)
    {
        DataTable dt = new DataTable("municipios");
        dt.Columns.Add("id_muni");
        dt.Columns.Add("nombre_muni");
        dt.Columns.Add("num_lideres_muni");
        dt.Columns.Add("meta_muni");
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader rs = null;
        RE_GenericBean rgb = null;
        try
        {



            conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=PacificStarBPO;Integrated Security=True;Pooling=False");
            conn.Open();


            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;

            comm.CommandText = "SELECT  id_muni, Upper(nombre_muni), num_lideres_muni, meta_muni FROM  municipios WHERE  (id_dep = @iddep)";
            comm.Parameters.Add("@iddep", SqlDbType.Int).Value = id_dep;
            rs = comm.ExecuteReader();
            rgb = new RE_GenericBean();
            while (rs.Read())
            {
                rgb = new RE_GenericBean();
                if (!rs.IsDBNull(0)) rgb.intC1 = rs.GetInt32(0);
                if (!rs.IsDBNull(1)) rgb.strC1 = rs.GetString(1);
                if (!rs.IsDBNull(2)) rgb.intC2 = rs.GetInt32(2);
                if (!rs.IsDBNull(3)) rgb.intC3 = rs.GetInt32(3);

                object[] objArr = { rgb.intC1, rgb.strC1, rgb.intC2, rgb.intC3 };
                dt.Rows.Add(objArr);
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message.ToString());
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return dt;
    }

    public static DataTable getDatosPatriota2(string where)
    {
        DataTable dt = new DataTable("patriota");
        dt.Columns.Add("nombre");
        dt.Columns.Add("celular");
        dt.Columns.Add("departamento");
        dt.Columns.Add("municipio");
        dt.Columns.Add("votos");
        dt.Columns.Add("meta");
        dt.Columns.Add("porcentaje");
        SqlConnection conn = null;
        SqlCommand comm = null;
        SqlDataReader rs = null;
        RE_GenericBean rgb = null;
        try
        {
            conn = OpenConnection();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;

            comm.CommandText = "SELECT   celula.nombre, celula.celular, departamento.nombre_dep, municipios.nombre_muni, celula.num_votos, 1111 AS Expr1, CONVERT(varchar, celula.num_votos * 100 / 1111) + ' % ' AS porcentaje FROM   municipios INNER JOIN departamento ON municipios.id_dep = departamento.id_dep INNER JOIN celula ON municipios.id_muni = celula.id_muni " + where;
            rs = comm.ExecuteReader();
            while (rs.Read())
            {
                rgb = new RE_GenericBean();
                if (!rs.IsDBNull(0)) rgb.strC1 = rs.GetString(0);
                if (!rs.IsDBNull(1)) rgb.strC2 = rs.GetString(1);
                if (!rs.IsDBNull(2)) rgb.strC3 = rs.GetString(2);
                if (!rs.IsDBNull(3)) rgb.strC4 = rs.GetString(3);
                if (!rs.IsDBNull(4)) rgb.intC1 = rs.GetInt32(4);
                if (!rs.IsDBNull(5)) rgb.intC2 = rs.GetInt32(5);
                if (!rs.IsDBNull(6)) rgb.strC5 = rs.GetString(6);
                object[] objArr = { rgb.strC1, rgb.strC2, rgb.strC3, rgb.strC4, rgb.intC1, rgb.intC2, rgb.strC5 };
                dt.Rows.Add(objArr);
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message.ToString());
            string ruta = "c:\\TEMP\\archivo.txt";
            StreamWriter escritor = new StreamWriter(ruta, true);
            escritor.WriteLine(e.Message.ToString());
            escritor.Close();
            return null;
        }
        finally
        {
            if (comm != null)
                comm.Dispose();
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return dt;
    }
}

